<?php


namespace app\services\statistic;


use app\facade\ExcelFacade;
use app\models\statistic\SaleStatisticModel;
use app\services\BaseService;
use think\facade\Db;


class StatisticService extends BaseService
{


    protected static function initModel()
    {
        // TODO: Implement initModel() method.
    }

    /**
     * 获取销售统计列表
     * @param $where
     * @param $page
     * @param $limit
     * @return mixed
     * @throws \think\db\exception\DbException
     */
    public static function getSaleStatisticListService($where, $page, $limit)
    {
        $list = SaleStatisticModel::getSaleListModel($where, $page, $limit);
        return $list;
    }

    /**
     * 获取销售统计列表EXCEL
     * @param $where
     * @param $page
     * @param $limit
     * @return mixed
     * @throws \think\db\exception\DbException
     */
    public static function getSaleStatisticListExcelService($where, $page, $limit)
    {
        $list = self::getSaleStatisticListService($where, $page, $limit);
        if (!$list['total']) {
            throw new \Exception('没有需要下载的数据');
        }
        $title = ['柜机商户', '联系电话', '联系人', '营业额(含未支付)(元)', '会员打折金额(元)', '优惠券优惠金额(元)', '商品促销金额(元)',
            '余额支付(元)', '在线支付(元)', '退款金额(元)', '未支付金额(元)'];
        $arr = ExcelFacade::createExcel('K', $title);
        $objSheet = $arr['objSheet'];
        foreach ($list['data'] as $k => $val) {
            $k = $k + 2;
            $objSheet->setCellValue('A' . $k, $val['franchiser_name'])
                ->setCellValue('B' . $k, $val['phone'])
                ->setCellValue('C' . $k, $val['contact'])
                ->setCellValue('D' . $k, $val['total_fee'])
                ->setCellValue('E' . $k, $val['vip_discount_amount'])
                ->setCellValue('F' . $k, $val['coupon_pay'])
                ->setCellValue('G' . $k, $val['product_sale_price'])
                ->setCellValue('H' . $k, $val['cash_pay'])
                ->setCellValue('I' . $k, $val['online_pay'])
                ->setCellValue('J' . $k, $val['refund_amount'])
                ->setCellValue('K' . $k, $val['no_pay']);
        }
        return $arr['excel'];
    }

    /**
     * 获取运营商列表
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function getOperatorListService()
    {
        $list = Db::connect('cupboard')->table('operator')->field('id,name')->order('id', 'desc')->select()->toArray();
        return $list;
    }

    /**
     * 根据运营商ID获取柜机商户
     * @param $operatorId
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function getFranchiserListByOperatorIdService($operatorId)
    {
        $list = Db::connect('cupboard')->table('franchiser')->field('id,name')
            ->where('operator_id', $operatorId)->whereNull('deleted_at')->order('id', 'desc')->select()->toArray();
        return $list;
    }

    /**
     * 根据柜机商ID获取模板
     * @param $franchiserId
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function getGroupListByFranchiserIdService($franchiserId)
    {
        $list = Db::connect('cupboard')->table('sub_group')->field('id,name')->where('franchiser_id', $franchiserId)->order('id', 'desc')->select()->toArray();
        return $list;
    }

    /**
     * 设备营业额
     * @param array $where
     * @param int $page
     * @param int $limit
     * @return array
     */
    public static function getCupboardSalesListService(array $where, int $page, int $limit): array
    {
        $field = 'o.id,sum(o.total_fee) AS sale_amount,count(o.id) as sale_count,o.cupboard_id,op.name as operator_name,
        f.name as franchiser_name,c.imei,c.serial,c.name as cupboard_name,c.province,c.city,c.district,c.address';
        $list = Db::connect('cupboard')
            ->table('order')
            ->alias('o')
            ->field($field)
            ->join('cupboard c', 'c.id = o.cupboard_id', 'left')
            ->join('franchiser f', 'f.id = o.franchiser_id', 'left')
            ->join('operator op', 'op.id = o.operator_id', 'left')
            ->where($where)
            ->group('o.cupboard_id')
            ->order('sale_amount', 'desc')
            ->paginate(['list_rows' => $limit, 'page' => $page])
            ->toArray();
        return $list;
    }

    /**
     * 获取设备营业额统计EXCEL
     * @param $where
     * @param $page
     * @param $limit
     * @return mixed
     * @throws \Exception
     */
    public static function getCupboardSalesListExcelService($where, $page, $limit)
    {
        $list = self::getCupboardSalesListService($where, $page, $limit);
        if (!$list['total']) {
            throw new \Exception('没有需要下载的数据');
        }
        $title = ['运营商', '柜机商户', '	设备IMEI', '设备名称', '设备编号', '营业额(元)', '设备地址'];
        $arr = ExcelFacade::createExcel('G', $title);
        $objSheet = $arr['objSheet'];
        foreach ($list['data'] as $k => $val) {
            $k = $k + 2;
            $objSheet->setCellValue('A' . $k, $val['operator_name'])
                ->setCellValue('B' . $k, $val['franchiser_name'])
                ->setCellValue('C' . $k, "\t" . $val['imei'])
                ->setCellValue('D' . $k, $val['cupboard_name'])
                ->setCellValue('E' . $k, "\t" . $val['serial'])
                ->setCellValue('F' . $k, number_format($val['sale_amount'] / 100, 2))
                ->setCellValue('G' . $k, $val['province'] . $val['city'] . $val['district'] . $val['address']);
        }
        return $arr['excel'];
    }

    /**
     * 订单统计
     * @param array $where
     * @param int $page
     * @param int $limit
     * @return array
     */
    public static function getOrderSalesListService(array $where, int $page, int $limit): array
    {
        $field = 'o.day,o.cupboard_count,o.online_cupboards,o.order_count,o.avg_order,o.order_user_count,o.avg_user_order,
        o.pay_amount,o.profit,o.avg_profit,o.refund_amount,op.name as operator_name,f.name as franchiser_name,f.province,
        f.city,f.district';
        $list = Db::connect('cupboard')
            ->table('franchiser_profit_stat')
            ->alias('o')
            ->field($field)
            ->join('franchiser f', 'f.id = o.franchiser_id', 'left')
            ->join('operator op', 'op.id = o.operator_id', 'left')
            ->where($where)
            ->order('o.day', 'desc')
            ->paginate(['list_rows' => $limit, 'page' => $page])
            ->toArray();
        return $list;
    }

    /**
     * 商品销售统计
     * @param array $where
     * @param int $page
     * @param int $limit
     * @return array
     */
    public static function productSaleStatisticsListService(array $where, int $page, int $limit): array
    {

        $field = 'opr.name,opr.order_id,opr.franchiser_id,opr.product_id,opr.type,opr.unit_sale_price AS product_price,
	    sum(opr.sale_price * o.vip_discount / 10000) as product_sum,sum(opr.buy_price) AS buy_amount,sum(opr.num) AS product_count';
        $extendFiled = $field . ',op.name as operator_name,f.name as franchiser_name,c.name as cate_name,p.buy_price';
        $list = Db::connect('cupboard')
            ->table('order_product_record')
            ->alias('opr')
            ->field($extendFiled)
            ->join('order o', 'o.id = opr.order_id', 'left')
            ->join('product p', 'p.id = opr.product_id', 'left')
            ->join('franchiser f', 'f.id = opr.franchiser_id', 'left')
            ->join('operator op', 'op.id = opr.operator_id', 'left')
            ->join('cat c', 'c.id = opr.cat_id', 'left')
            ->where($where)
            ->group('opr.product_id, opr.franchiser_id')
            ->order('product_count', 'desc')
            ->paginate(['list_rows' => $limit, 'page' => $page])
            ->toArray();
        return $list;
    }

    /**
     * 商品销售详情
     * @param int $productID
     * @param string $startTime
     * @param string $endTime
     * @param int $page
     * @param int $limit
     * @return array
     */
    public static function productSaleDetailService(int $productID, string $startTime, string $endTime, int $page, int $limit): array
    {
        $field = 'opr.name,opr.type,p.buy_price,sum(opr.num) as num,opr.unit_sale_price,IFNULL(SUM(case when opr.type = 2 
        then opr.unit_sale_price / 100 * opr.num / 500 else opr.unit_sale_price / 100 * opr.num end), 0) AS p_total_price,
        opr.created_at,sum(opr.buy_price) AS buy_amount';
        $extendFiled = $field . ',op.name as operator_name,f.name as franchiser_name,c.name as cate_name,d.name as cupboard_name,
        d.imei,d.serial';
        $list = Db::connect('cupboard')
            ->table('order_product_record')
            ->alias('opr')
            ->field($extendFiled)
            ->join('order o', 'o.id = opr.order_id', 'left')
            ->join('product p', 'p.id = opr.product_id', 'left')
            ->join('franchiser f', 'f.id = opr.franchiser_id', 'left')
            ->join('operator op', 'op.id = opr.operator_id', 'left')
            ->join('cat c', 'c.id = opr.cat_id', 'left')
            ->leftJoin('cupboard d', 'd.id = opr.cupboard_id')
            ->where('opr.product_id', $productID)
            ->whereBetweenTime('opr.created_at', $startTime, $endTime)
            ->whereNull('opr.deleted_at')
            ->group('opr.order_id')
            ->order('opr.order_id', 'desc')
            ->paginate(['list_rows' => $limit, 'page' => $page])
            ->toArray();
        return $list;
    }

    /**
     * 设备销量统计
     * @param array $where
     * @param int $page
     * @param int $limit
     * @return array
     */
    public static function deviceSaleStatisticsListService(array $where, int $page, int $limit): array
    {

        $field = 'opr.name,opr.type,sum(opr.buy_price) as buy_amount,sum(case when opr.type = 2 then opr.weight else opr.num end) AS sale_count,
        sum(opr.sale_price) AS sale_amount,opr.cupboard_id,opr.product_id';
        $extendFiled = $field . ',op.name as operator_name,f.name as franchiser_name,c.name as cate_name,d.name as cupboard_name,d.serial';
        $list = Db::connect('cupboard')
            ->table('order_product_record')
            ->alias('opr')
            ->field($extendFiled)
            ->join('order o', 'o.id = opr.order_id', 'left')
            ->join('franchiser f', 'f.id = opr.franchiser_id', 'left')
            ->join('operator op', 'op.id = opr.operator_id', 'left')
            ->join('cat c', 'c.id = opr.cat_id', 'left')
            ->leftJoin('cupboard d', 'd.id = opr.cupboard_id')
            ->where($where)
            ->whereNull('opr.deleted_at')
            ->group('opr.product_id, opr.cupboard_id')
            ->order('sale_amount', 'desc')
            ->paginate(['list_rows' => $limit, 'page' => $page])
            ->toArray();
        return $list;
    }

    /**
     * 商品销售详情
     * @param int $productID
     * @param int $cupboardId
     * @param string $startTime
     * @param string $endTime
     * @param int $page
     * @param int $limit
     * @return array
     */
    public static function deviceSaleDetailService(int $productID, int $cupboardId, string $startTime, string $endTime, int $page, int $limit): array
    {
        $field = 'opr.name,opr.type,p.buy_price,sum(opr.num) as num,opr.unit_sale_price,IFNULL(SUM(case when opr.type = 2 
        then opr.unit_sale_price / 100 * opr.num / 500 else opr.unit_sale_price / 100 * opr.num end), 0) AS p_total_price,
        opr.created_at,sum(opr.buy_price) AS buy_amount';
        $extendFiled = $field . ',op.name as operator_name,f.name as franchiser_name,c.name as cate_name,d.name as cupboard_name,
        d.imei,d.serial';
        $list = Db::connect('cupboard')
            ->table('order_product_record')
            ->alias('opr')
            ->field($extendFiled)
            ->join('order o', 'o.id = opr.order_id', 'left')
            ->join('product p', 'p.id = opr.product_id', 'left')
            ->join('franchiser f', 'f.id = opr.franchiser_id', 'left')
            ->join('operator op', 'op.id = opr.operator_id', 'left')
            ->join('cat c', 'c.id = opr.cat_id', 'left')
            ->leftJoin('cupboard d', 'd.id = opr.cupboard_id')
            ->where('opr.product_id', $productID)
            ->where('opr.cupboard_id', $cupboardId)
            ->whereBetweenTime('opr.created_at', $startTime, $endTime)
            ->whereNull('opr.deleted_at')
            ->group('opr.order_id')
            ->order('opr.order_id', 'desc')
            ->paginate(['list_rows' => $limit, 'page' => $page])
            ->toArray();
        return $list;
    }
}